# data

from openpyxl import load_workbook
import os

file_xlsx = ""
data_list = []
dict_usage_count = {}
dict_answer_count = {}
list_show = []

# 获取当前目录下的*.xlsx 文件名称，注意目录下只能放置一个*.xlsx
for root, dirs, files in os.walk(os.getcwd()):     # 分别代表根目录、文件夹、文件
        for file in files:                         # 遍历文件
            #print(file)
            if ".xlsx" in file:
                print(1)
                print(file)
                file_xlsx = file
            else:
                ...


# excel表格获取

# 1.打开 Excel 表格并获取表格名称
workbook = load_workbook(filename=file_xlsx)

# 2.获取表格的尺寸大小(几行几列数据)
sheet1 = workbook["default"]
print(sheet1.dimensions)



####获取数据

#呼出主叫
call_numbers = sheet1["W"]

#通话时间
call_times = sheet1["E"]

#有连接接通时间
call_connect = sheet1["V"]

#挂断原因
call_statuss = sheet1["H"]

#数据存储到list
for call_number,call_time,call_status in zip(call_numbers[1:],call_times[1:],call_statuss[1:]):
    data_list.append([call_number.value,call_time.value,call_status.value])

#print(data_list)


######分析数据：

#号码 呼叫总量 统计
def usage_count():
    global dict_usage_count
    list_temp = []
    for i in data_list:
           list_temp.append(i[0])

    print("总号码个数：", str(len(set(list_temp))-1))

    for call_numbers in set(list_temp):
        dict_usage_count[call_numbers] = list_temp.count(call_numbers)
        #print((call_numbers, list_temp.count(call_numbers)))


#有接通时间统计
def answer_count():
    global dict_answer_count
    list_temp = []
    for i in data_list:
        if int(i[1])>0:
            list_temp.append(i[0])
    print("通话码个数：", str(len(set(list_temp)) - 1))

    for call_numbers in set(list_temp):
        dict_answer_count[call_numbers]  = list_temp.count(call_numbers)
        #print((call_numbers, list_temp.count(call_numbers)))


#为可视化组装数据
def show_data():
    global list_show
    for number in dict_usage_count:
        if number in dict_answer_count:
            list_show.append([number,dict_usage_count[number],dict_answer_count[number]])
        else:
            list_show.append([number, dict_usage_count[number], 0])
    list_show = sorted(list_show, key=(lambda x: x[2]), reverse=False)  ##按第三个元素
    list_show.append(["___________________"])
    list_show.append("总号码个数  {}".format(str(len(list_show)-1)))
    list_show.append("总话单量   {}".format(str(len(data_list))))
    list_show.append("文件名称   {}".format(file_xlsx))



# main
def start():
    global list_show
    usage_count()
    answer_count()
    show_data()
    return list_show
